import pandas as pd
import csv
from pypinyin import lazy_pinyin
import sys
from pathlib import Path
import uuid


def get_abbreviation(text):
    pinyin_list = lazy_pinyin(text)
    return ''.join([p[0] for p in pinyin_list if p.isalpha()])


def generate_account_name(area):
    pinyin_list = lazy_pinyin(area)
    if len(area) <= 3:
        return ''.join(pinyin_list).lower()
    else:
        return ''.join(pinyin_list[:2]) + ''.join([p[0] for p in pinyin_list[2:]]).lower()


def generate_full_pinyin_account_name(area):
    pinyin_list = lazy_pinyin(area)
    return ''.join(pinyin_list).lower()


def clean_data(df):
    for col in df.columns:
        if df[col].dtype == object:
            df[col] = df[col].astype(str).str.strip().str.replace(' ', '')
    return df


def process_sheet(excel_file, sheet_num):
    try:
        df = pd.read_excel(excel_file, sheet_name=sheet_num, header=1)

        if sheet_num == 0:
            df = df[['地市', '区县', '街道', '管理员姓名', '手机号']]
            df['地址合并'] = df['地市'] + df['区县'] + df['街道']
            final_col = '街道'
        elif sheet_num == 1:
            df = df[['地市', '区县', '管理员姓名', '手机号']]
            df['地址合并'] = df['地市'] + df['区县']
            final_col = '区县'
        else:
            df = df[['地市', '管理员姓名', '手机号']]
            df['地址合并'] = df['地市']
            final_col = '地市'

        df['地址缩写'] = df['地址合并'].apply(get_abbreviation)
        df['固定数字'] = '1783411255105200129'

        if sheet_num == 2:
            df['账号'] = df[final_col].apply(generate_full_pinyin_account_name)
        else:
            df['账号'] = df[final_col].apply(generate_account_name)

        df = clean_data(df)

        return df[['地址合并', '账号', '管理员姓名', '固定数字', '手机号', final_col]].rename(
            columns={final_col: '地区'})

    except Exception as e:
        print(f"处理工作表 {sheet_num} 出错: {str(e)}")
        return None


def main(excel_path):
    print("测试输出日志")
    try:
        # 确保输出目录存在（使用绝对路径）
        output_dir = Path(r'C:\outputfile').absolute()
        output_dir.mkdir(parents=True, exist_ok=True)
        # 生成带日期时分秒和UUID的文件名
        timestamp = pd.Timestamp.now().strftime('%Y%m%d%H%M%S')
        unique_id = uuid.uuid4().hex
        output_path = output_dir / f"merged_{timestamp}_{unique_id}.csv"

        # 处理三个工作表
        df_sheet3 = process_sheet(excel_path, 2)
        df_sheet2 = process_sheet(excel_path, 1)
        df_sheet1 = process_sheet(excel_path, 0)

        if df_sheet3 is not None and df_sheet2 is not None and df_sheet1 is not None:
            merged_df = pd.concat([df_sheet3, df_sheet2, df_sheet1], ignore_index=True)

            # 保存CSV（添加newline=''避免Windows多余空行）
            with open(output_path, 'w', newline='', encoding='utf-8') as f:
                merged_df.to_csv(f, index=False, header=False,
                                 quoting=csv.QUOTE_NONE, escapechar='\\')

            # 返回路径
            return str(output_path)
        else:
            raise Exception("合并失败：至少一个工作表处理结果为空")

    except Exception as e:
        print(f"处理失败：{str(e)}")
        return None


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("错误：请提供Excel文件路径作为参数")
        sys.exit(1)

    excel_path = sys.argv[1]
    csv_path = main(excel_path)
    if csv_path:
        print(csv_path)
    else:
        sys.exit(1)